﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace Adams.Admin.DataAccess.Donations
{
    public static class Index
    {
        public static Adams.Admin.DataTransfer.Donations.Index.PageLoad PageLoad()
        {
            var data = new Adams.Admin.DataTransfer.Donations.Index.PageLoad();
            
            return data;
        }

        public static Adams.Admin.DataTransfer.Donations.Index.OneTimeGridLoad OneTimeGridLoad(DataTransfer.GridLoadInput gridinput)
        {
            var data = new Adams.Admin.DataTransfer.Donations.Index.OneTimeGridLoad();
            data.Records = new Shared.Entities.Donations.OneTimeCollection();

            string filter = string.IsNullOrWhiteSpace(gridinput.Search) ? string.Empty : "M.Email LIKE '%" + gridinput.Search.Replace("'", "''") + "%' OR M.FirstName LIKE '%" + gridinput.Search.Replace("'", "''") + "%' OR M.LastName LIKE '%" + gridinput.Search.Replace("'", "''") + "%'";

            string dbq = DB.BuildPagingQuery(gridinput.StartRow, gridinput.PageSize,
                "O.DonationID,O.BucketID,O.MemberID,O.Amount,O.EnteredOn,O.StatusID,B.Name AS BucketName, M.Email, M.FirstName, M.LastName",
                "Donations.OneTime O INNER JOIN Donations.Buckets B ON O.BucketID = B.BucketID INNER JOIN Membership.Members M ON O.MemberID = M.MemberID",
                filter, string.Empty, gridinput.Sort + " " + gridinput.SortDirection, "DonationID");

            using (SqlDataReader dr = DB.RunDataReader(dbq, CommandType.Text))
            {
                dr.Read();
                data.TotalRecords = dr[0].DbInt();

                dr.NextResult();
                while (dr.Read())
                {
                    var onetime = new Shared.Entities.Donations.OneTime();
                    onetime.ID = dr["DonationID"].DbInt();
                    onetime.Amount = dr["Amount"].DbDecimal();
                    onetime.EnteredOn = dr["EnteredOn"].DbDate();
                    onetime.Status = (Shared.Entities.Donations.Status)dr["StatusID"].DbInt();

                    onetime.Bucket = new Shared.Entities.Donations.Bucket() {
                        ID = dr["BucketID"].DbInt(),
                        Name = dr["BucketName"].DbString()
                    };

                    onetime.Member = new Shared.Entities.Membership.Member
                    {
                        ID = dr["MemberID"].DbInt(),
                        Email = dr["Email"].DbString(),
                        FirstName = dr["FirstName"].DbString(),
                        LastName = dr["LastName"].DbString()
                    };

                    data.Records.Add(onetime);
                }
            }

            return data;
        }

        public static Adams.Admin.DataTransfer.Donations.Index.RecurringGridLoad RecurringGridLoad(DataTransfer.GridLoadInput gridinput)
        {
            var data = new Adams.Admin.DataTransfer.Donations.Index.RecurringGridLoad();
            data.Records = new Shared.Entities.Donations.RecurringCollection();

            string filter = string.IsNullOrWhiteSpace(gridinput.Search) ? string.Empty : "M.Email LIKE '%" + gridinput.Search.Replace("'", "''") + "%' OR M.FirstName LIKE '%" + gridinput.Search.Replace("'", "''") + "%' OR M.LastName LIKE '%" + gridinput.Search.Replace("'", "''") + "%'";

            string dbq = DB.BuildPagingQuery(gridinput.StartRow, gridinput.PageSize,
                "O.DonationID,O.BucketID,O.MemberID,O.Amount,O.EnteredOn,O.StatusID,B.Name AS BucketName, M.Email, M.FirstName, M.LastName",
                "Donations.Recurring O INNER JOIN Donations.Buckets B ON O.BucketID = B.BucketID INNER JOIN Membership.Members M ON O.MemberID = M.MemberID",
                filter, string.Empty, gridinput.Sort + " " + gridinput.SortDirection, "DonationID");

            using (SqlDataReader dr = DB.RunDataReader(dbq, CommandType.Text))
            {
                dr.Read();
                data.TotalRecords = dr[0].DbInt();

                dr.NextResult();
                while (dr.Read())
                {
                    var onetime = new Shared.Entities.Donations.Recurring();
                    onetime.ID = dr["DonationID"].DbInt();
                    onetime.Amount = dr["Amount"].DbDecimal();
                    onetime.EnteredOn = dr["EnteredOn"].DbDate();
                    onetime.Status = (Shared.Entities.Donations.Status)dr["StatusID"].DbInt();

                    onetime.Bucket = new Shared.Entities.Donations.Bucket()
                    {
                        ID = dr["BucketID"].DbInt(),
                        Name = dr["BucketName"].DbString()
                    };

                    onetime.Member = new Shared.Entities.Membership.Member
                    {
                        ID = dr["MemberID"].DbInt(),
                        Email = dr["Email"].DbString(),
                        FirstName = dr["FirstName"].DbString(),
                        LastName = dr["LastName"].DbString()
                    };

                    data.Records.Add(onetime);
                }
            }

            return data;
        }
    }
}
